/*
 * Copy right @ Ericsson Global Services Limited.
 */
package com.ericsson.mna.model.bs;

import java.io.InputStream;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.Map;

import com.ericsson.mna.model.dao.OracleConnectionFactory;
import com.ericsson.mna.model.pojo.AssignTask;
import com.ericsson.mna.model.pojo.DBConf;
import com.ericsson.mna.model.pojo.Task;


/**
 * The Class TaskServices.
 */
public class TaskServices {

	/** The db conf. */
	public DBConf dbConf;

	/**
	 * Instantiates a new task services.
	 *
	 * @param DbConf the db conf
	 */
	public TaskServices(DBConf DbConf) {
			dbConf=DbConf;
		}

	/**
	 * Assign task.
	 *
	 * @param at the at
	 * @return the int
	 */
	public  int assignTask(AssignTask at, InputStream is) {
		int value=0;
		int x=enquiryIdGenerator();
		String id="TASK"+x;
		//cu.setUserId(id);
		String nextAction="Next Action";

		Connection con=null;
		PreparedStatement pst=null;

		//Creating connection
		OracleConnectionFactory ocf=OracleConnectionFactory.getInstance();
		con=ocf.accessDB(dbConf);
		String sql="INSERT INTO task_todo VALUES (?,sysdate,?,?,?,?,?,?,?,sysdate,?,?,?,?,?,null,null )";
		String sql_task_visibility="INSERT INTO task_visibility_todo values(?,?,?)";
		System.out.println(sql);
		try {
			pst=con.prepareStatement(sql);
			pst.setString(1, id);
			pst.setString(2, at.getCategory());
			pst.setString(3, at.getTaskDescription());
			pst.setDate(4, at.getTaskEndDate());
			pst.setString(5, "nextAction");
			pst.setString(6, at.getAssignedTo());
			pst.setString(7, at.getAssignedBy());
			pst.setString(8, at.getPriority());
			pst.setString(9, at.getStatus());
			pst.setString(10,at.getDomain());
			pst.setString(11,at.getSubDomain());
			pst.setString(12,at.getTitle());
			pst.setBlob(13, is);
			//pst.setString(10, "1-1-15");
			value=pst.executeUpdate();
			con.commit();
			if(value>0)
			{
			pst=con.prepareStatement(sql_task_visibility);
			pst.setString(1,id);
			pst.setString(3, at.getStatus());
			pst.setString(2, at.getAssignedTo());
			value=pst.executeUpdate();
			con.commit();
			}
		} catch (SQLException e) {

			e.printStackTrace();
		}

		 finally {
				try {
					con.close();
				} catch (SQLException sqe) {
					System.out.println("Exception caught in Assign Task :"+sqe);
				}
			}

			return value;
	}

    public boolean updateTask(AssignTask assignTask)
    {
    	Connection con=null;
		PreparedStatement pst=null;
        int val;
		//Creating connection
		OracleConnectionFactory ocf=OracleConnectionFactory.getInstance();
		con=ocf.accessDB(dbConf);
		String sql="UPDATE task_todo  set comments=?,status=?,TASK_COMPLETED_ON=? where task_id=?";
		try {
			pst=con.prepareStatement(sql);
			pst.setString(1, assignTask.getComments());
			pst.setString(2, assignTask.getStatus());
			pst.setDate(3, assignTask.getTaskCompletedOn());
			pst.setString(4, assignTask.getTask_id());
			val=pst.executeUpdate();
			con.commit();
			if(val>0)
			{
				return true;
			}

			} catch (SQLException e) {

			e.printStackTrace();
		}

		 finally {
				try {
					con.close();
				} catch (SQLException sqe) {
					System.out.println("Exception caught in Assign Task :"+sqe);
				}
			}





    	return false;
    }

/**
 * Enquiry id generator.
 *
 * @return the int
 */
public int enquiryIdGenerator()
{
	int id=0;
	//Creating connection
	    Connection con=null;
		OracleConnectionFactory ocf=OracleConnectionFactory.getInstance();
		con=ocf.accessDB(dbConf);
	try{
	String qry="select createUser_seq.nextval from dual";
	Statement st=con.createStatement();
	ResultSet rs= st.executeQuery(qry);
	rs.next();
	id=rs.getInt(1);
	}
	catch(SQLException sqe)
	{
		sqe.printStackTrace();

	}
	finally{
		try {
			con.close();
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
	}
	return id;
}

public ArrayList<Task> getTaskList(String userId)
{
	ResultSet rs=null;
	Connection con=null;
	PreparedStatement pst=null;

	//Creating connection
	OracleConnectionFactory ocf=OracleConnectionFactory.getInstance();
	con=ocf.accessDB(dbConf);
	ArrayList<Task> taskList=new ArrayList<Task>();
	Task task=null;
	try {

		String sql="select * from task_todo where task_Id in( select task_Id from task_visibility_todo where visible_to=?) ";
		pst=con.prepareStatement(sql);
		pst.setString(1,userId);
		rs=pst.executeQuery();
		/*if(rs!=null)
		{*/
		while(rs.next())
		{
			task=new Task();
			task.setTask_Id(rs.getString(1));
			task.setDate_Logged(rs.getDate(2).toString());
			task.setCatagory(rs.getString(3));
			task.setTask_Description(rs.getString(4));
			task.setTask_End_Date(rs.getDate(5).toString());
			task.setNext_Action(rs.getString(6));
			task.setAssingn_To(rs.getString(7));
			task.setAssigned_By(rs.getString(8));
			task.setPriority(rs.getString(9));
			/*task.setTask_Completed_On(rs.getDate(10).toString());*/
			task.setStatus(rs.getString(11));
			task.setDomain(rs.getString(12));
			task.setSub_Domain(rs.getString(13));
			task.setTitle(rs.getString(14));
			task.setSubCategory(rs.getString(17));
			System.out.println("From Task Services..inserting task:::"+task);
			taskList.add(task);

		}
		/*}
		else
		{
			taskList=null;
		}
		*/

	}
	catch (SQLException sqe)
	{
		System.out.println("Exception caught in Task Service BS :"+sqe);
		sqe.printStackTrace();
	}
	finally
	{
		try
		{
			con.close();
		} catch (SQLException sqe) {
			System.out.println("Exception caught in Task Service BS :"+sqe);
		}
	}
	System.out.println("Task Details from task services:::"+taskList);
	return taskList;
}

public  ResultSet getCategory() {

	Connection con=null;
	Statement stmt=null;
	ResultSet rs = null;
	//Creating connection
	OracleConnectionFactory ocf=OracleConnectionFactory.getInstance();
	con=ocf.accessDB(dbConf);
	String sql="SELECT CATEGORY_NAME FROM category_todo";
	System.out.println(sql);
	try {
		stmt = con.createStatement();
		rs = stmt.executeQuery(sql);
	} catch (SQLException e) {

		e.printStackTrace();
	}

		return rs;
}

public  ResultSet getSubCategory(String domain) {

	Connection con=null;
	PreparedStatement pst=null;
	ResultSet rs = null;
	OracleConnectionFactory ocf=OracleConnectionFactory.getInstance();
	con=ocf.accessDB(dbConf);
	String sql="SELECT SUB_CATEGORY FROM category_todo WHERE CATEGORY_NAME=?";
	System.out.println(sql);
	try {
		pst=con.prepareStatement(sql);
		pst.setString(1,domain);
		rs=pst.executeQuery();
	} catch (SQLException e) {

		e.printStackTrace();
	}

		return rs;
}

public  ResultSet getUserId(String user) {


	Connection con=null;
	PreparedStatement pst=null;
	ResultSet rs = null;
	String userId = user + "%";
	//Creating connection
	OracleConnectionFactory ocf=OracleConnectionFactory.getInstance();
	con=ocf.accessDB(dbConf);
	String sql="SELECT USER_ID FROM user_details_todo WHERE UPPER(USER_ID) like UPPER(?)";
	System.out.println(sql);
	try {
		pst=con.prepareStatement(sql);
		pst.setString(1,userId);
		rs=pst.executeQuery();
	} catch (SQLException e) {

		e.printStackTrace();
	}

		return rs;
}

public ArrayList<Task> getTaskStatus(String userId)
{
	Connection con=null;
	PreparedStatement pst=null;
	ResultSet rs = null;
	Task task=null;
	//Creating connection
	OracleConnectionFactory ocf=OracleConnectionFactory.getInstance();
	con=ocf.accessDB(dbConf);
	ArrayList<Task> taskList=new ArrayList<Task>();
	String sql="select * from task_todo where assigned_by=?";
	try {
		pst=con.prepareStatement(sql);
		pst.setString(1,userId);
		rs=pst.executeQuery();

		while(rs.next())
		{

			task=new Task();
			task.setTask_Id(rs.getString(1));
			task.setDate_Logged(rs.getDate(2).toString());
			task.setCatagory(rs.getString(3));
			task.setTask_Description(rs.getString(4));
			task.setTask_End_Date(rs.getDate(5).toString());
			task.setNext_Action(rs.getString(6));
			task.setAssingn_To(rs.getString(7));
			task.setAssigned_By(rs.getString(8));
			task.setPriority(rs.getString(9));
			/*task.setTask_Completed_On(rs.getDate(10).toString());*/
			task.setStatus(rs.getString(11));
			task.setDomain(rs.getString(12));
			task.setSub_Domain(rs.getString(13));
			task.setTitle(rs.getString(14));
			task.setSubCategory(rs.getString(17));
			System.out.println("From Task Services..get task:::"+task);
			taskList.add(task);
		}

	}
	catch (SQLException e) {
		// TODO Auto-generated catch block
		e.printStackTrace();
	}

	return taskList;
}

public boolean updateTaskStatus(HashMap<String, String> taskStatusMap) throws SQLException
{
	Connection con=null;
	PreparedStatement pst=null;

	//Creating connection
	OracleConnectionFactory ocf=OracleConnectionFactory.getInstance();
	con=ocf.accessDB(dbConf);
	con.setAutoCommit(false);
	String sql="UPDATE task_todo  set status=? where task_id=?";
	try {
			pst=con.prepareStatement(sql);
			for(Map.Entry<String,String> entry : taskStatusMap.entrySet()){
				pst.setString(1, entry.getValue());
				pst.setString(2, entry.getKey());
				pst.executeUpdate();
			}
			con.commit();
		} catch (SQLException e) {
			con.rollback();
			e.printStackTrace();
			return false;
	}

	 finally {
			try {
				con.close();
			} catch (SQLException sqe) {
				System.out.println("Exception caught in Assign Task :"+sqe);
			}
		}

	return true;
}
}
